# Data Warehouses

Jitsu supports following Data Warehouses (DWH) as destinations:

* [Postgres SQL](#postgres-sql)
* [ClickHouse](#clickhouse)
* [Redshift](#redshift)
* [Snowflake](#snowflake)
* [BigQuery](#bigquery)
* [Mysql](#mysql)

This article explains which features are available for each DWH and implementation details.

## Postgres SQL

| Feature                                      | Status     | Details                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|:---------------------------------------------|:-----------|:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Push events (API keys)<br/>**stream mode**   | Supported  | **If destination table has primary key:**<br/>`INSERT INTO destination_table(...) VALUES ... ON CONFLICT ON CONSTRAINT primary_key DO UPDATE SET ...`<br/>**If destination table has no primary key:**<br/>`INSERT INTO destination_table(...) VALUES ...`                                                                                                                                                                                                   |
| Push events (API keys)<br/>**batch mode**    | Supported  | **If destination table has primary key:**<br/>In SQL transaction:<br/>1. create `tmp_table` <br/>2. insert multiple rows into `tmp_table` <br/>3. insert from tmp_table: `INSERT INTO destination_table(...) SELECT ... FROM tmp_table ON CONFLICT ON CONSTRAINT primary_key DO UPDATE SET ...`<br/>4. delete `tmp_table`<br/><br/>**If destination table has no primary key:**<br/>In SQL transaction:<br/>1. insert multiple rows into destination table   |
| Pull events (Sources)                        | Supported* | *JavaScript Transformation currently not applied to pulled data<br/><br/>In SQL transaction:<br/>1. Delete previous data for current sync interval: `DELETE FROM destination_table WHERE _time_interval=?`<br/>2. See **Push events (API keys) batch mode**                                                                                                                                                                                                  |
| User Recognition<br/>**stream mode**         | Supported  | Primary key on `eventn_ctx_event_id` field is required for destination table.<br/>`UPDATE destination_table SET .. WHERE eventn_ctx_event_id=?`                                                                                                                                                                                                                                                                                                              |
| User Recognition<br/>**batch mode**          | Supported  | Primary key on `eventn_ctx_event_id` field is required for destination table.<br/>Same as **Push events (API keys) batch mode**                                                                                                                                                                                                                                                                                                                              |

## Clickhouse

| Feature                                      | Status     | Details                                                                                                                                                                                                                                    |
|:---------------------------------------------|:-----------|:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Push events (API keys)<br/>**stream mode**   | Supported  | `INSERT INTO destination_table (...) VALUES ...`                                                                                                                                                                                           |
| Push events (API keys)<br/>**batch mode**    | Supported  | `INSERT INTO destination_table (...) VALUES ...`                                                                                                                                                                                           |
| Pull events (Sources)                        | Supported* | *JavaScript Transformation currently not applied to pulled data<br/><br/>1. Delete previous data for current sync interval: `ALTER TABLE destination_table DELETE WHERE _time_interval=?`<br/>2. See **Push events (API keys) batch mode** |
| User Recognition<br/>**stream mode**         | Supported* | `INSERT INTO destination_table (...) VALUES ...`<br/>*Read more about [Clickhouse specifics](/docs/other-features/retroactive-user-recognition)                                                                                            |
| User Recognition<br/>**batch mode**          | Supported* | `INSERT INTO destination_table (...) VALUES ...`<br/>*Read more about [Clickhouse specifics](/docs/other-features/retroactive-user-recognition)                                                                                            |

## Redshift

| Feature                                      | Status     | Details                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|:---------------------------------------------|:-----------|:--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Push events (API keys)<br/>**stream mode**   | Supported  | `INSERT INTO destination_table(...) VALUES ...`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| Push events (API keys)<br/>**batch mode**    | Supported  | 1. Upload file with data to S3<br/>2. Copy data from S3 to destination table: `copy destination_table from 's3://...'`<br/>3. Delete data from S3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| Pull events (Sources)                        | Supported* | *JavaScript Transformation currently not applied to pulled data<br/><br/>**If destination table has primary key:**<br/>In SQL transaction:<br/>1. Delete previous data for current sync interval: `DELETE FROM destination_table WHERE _time_interval=?`<br/>2. create `tmp_table` <br/>3. insert multiple rows into `tmp_table`<br/>4. delete from destination table duplicated rows `DELETE FROM destination_table using tmp_table where primary_key_columns=?` .<br/>5. insert from tmp_table: `INSERT INTO destination_table (...) select ... from tmp_table`<br/>6. delete `tmp_table`<br/><br/>**If destination table has no primary key:**<br/>In SQL transaction:<br/>1. Delete previous data for current sync interval: `DELETE FROM destination_table WHERE _time_interval=?`<br/>2. insert multiple rows into destination table  |
| User Recognition<br/>**stream mode**         | Supported  | Primary key on `eventn_ctx_event_id` field is required for destination table.<br/>`UPDATE destination_table SET .. WHERE eventn_ctx_event_id=?`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| User Recognition<br/>**batch mode**          | Supported  | Primary key on `eventn_ctx_event_id` field is required for destination table.<br/>In SQL transaction:<br/>1. create `tmp_table` <br/>2. insert multiple rows into `tmp_table`<br/>3. delete from destination table duplicated rows `DELETE FROM destination_table using tmp_table where eventn_ctx_event_id=?` .<br/>4. insert from tmp_table: `INSERT INTO destination_table (...) select ... from tmp_table`<br/>5. delete `tmp_table`                                                                                                                                                                                                                                                                                                                                                                                                    |

## Snowflake

<Hint>
    Please carefully read Snowflake docs regarding <a href="https://docs.snowflake.com/en/user-guide/admin-usage-billing.html">System Usage & Billing</a>,
    especially the <a href="https://docs.snowflake.com/en/user-guide/credits.html#virtual-warehouse-credit-usage">Virtual Warehouse Credit Usage</a> part.
    <br></br>Remember that using Snowflake destination in <code inline="true">stream</code> mode is highly likely to be charged as full-time warehouse usage.
    <br></br>For <code inline="true">batch</code> mode, we recommend enabling <a href="https://docs.snowflake.com/en/user-guide/warehouses-considerations.html#automating-warehouse-suspension">Automating Warehouse Suspension</a> using <a href="https://docs.snowflake.com/en/sql-reference/sql/alter-warehouse.html">ALTER WAREHOUSE</a> query. E.g.:<br></br>
    <code inline="false">ALTER WAREHOUSE WAREHOUSE_NAME set AUTO_SUSPEND = 1</code>
</Hint>

| Feature                                      | Status     | Details                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|:---------------------------------------------|:-----------|:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Push events (API keys)<br/>**stream mode**   | Supported  | `INSERT INTO destination_table(...) VALUES ...`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| Push events (API keys)<br/>**batch mode**    | Supported  | **S3**:<br/>1. Upload file with data to S3<br/>2. Copy data from S3 to destination table: `copy into destination_table (...) from 's3://...'`<br/>3. Delete data from S3<br/><br/>**Google Cloud Storage**:<br/>1. Upload file with data to GCS<br/>2. Copy data from GCS to destination table: `copy into destination_table (...) from @...`<br/>3. Delete data from GCS                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| Pull events (Sources)                        | Supported* | *JavaScript Transformation currently not applied to pulled data<br/><br/>**If destination table has primary key:**<br/>In SQL transaction:<br/>1. Delete previous data for current sync interval: `DELETE FROM destination_table WHERE _time_interval=?`<br/>2. create `tmp_table` <br/>3. insert multiple rows into `tmp_table`<br/>4. merge into destination table: `MERGE INTO destination_table USING (SELECT ... FROM tmp_table) ON primary_key_columns=? WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT (...) VALUES (...)`<br/>5. delete `tmp_table`<br/><br/>**If destination table has no primary key:**<br/>In SQL transaction:<br/>1. Delete previous data for current sync interval: `DELETE FROM destination_table WHERE _time_interval=?`<br/>2. insert multiple rows into destination table  |
| User Recognition<br/>**stream mode**         | Supported  | Primary key on `eventn_ctx_event_id` field is required for destination table.<br/>`UPDATE destination_table SET .. WHERE eventn_ctx_event_id=?`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| User Recognition<br/>**batch mode**          | Supported  | Primary key on `eventn_ctx_event_id` field is required for destination table.<br/>In SQL transaction:<br/>1. Delete previous data for current sync interval: `DELETE FROM destination_table WHERE _time_interval=?`<br/>2. create `tmp_table` <br/>3. insert multiple rows into `tmp_table`<br/>4. merge into destination table: `MERGE INTO destination_table USING (SELECT ... FROM tmp_table) ON eventn_ctx_event_id=? WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT (...) VALUES (...)`<br/>5. delete `tmp_table`                                                                                                                                                                                                                                                                                      |

## BigQuery

| Feature                                      | Status          | Details                                                                                                                       |
|:---------------------------------------------|:----------------|:------------------------------------------------------------------------------------------------------------------------------|
| Push events (API keys)<br/>**stream mode**   | Supported       | Insert using bigquery API                                                                                                     |
| Push events (API keys)<br/>**batch mode**    | Supported       | 1. Upload file with data to GCS<br/>2. Copy data from GCS to destination table using bigquery API<br/>3. Delete data from GCS |
| Pull events (Sources)                        | Not supported   |                                                                                                                               |
| User Recognition<br/>**stream mode**         | Not supported   |                                                                                                                               |
| User Recognition<br/>**batch mode**          | Not supported   |                                                                                                                               |

## Mysql

| Feature                                      | Status     | Details                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|:---------------------------------------------|:-----------|:------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Push events (API keys)<br/>**stream mode**   | Supported  | **If destination table has primary key:**<br/>`INSERT INTO destination_table(...) VALUES ... ON DUPLICATE KEY UPDATE ...`<br/>**If destination table has no primary key:**<br/>`INSERT INTO destination_table(...) VALUES ...`                                                                                                                                                                                                                        |
| Push events (API keys)<br/>**batch mode**    | Supported  | **If destination table has primary key:**<br/>In SQL transaction:<br/>1. create `tmp_table` <br/>2. insert multiple rows into `tmp_table` <br/>3. insert from tmp_table: `INSERT INTO destination_table(...) SELECT * FROM (SELECT ... FROM tmp_table) AS tmp ON DUPLICATE KEY UPDATE ...`<br/>4. delete `tmp_table`<br/><br/>**If destination table has no primary key:**<br/>In SQL transaction:<br/>1. insert multiple rows into destination table |
| Pull events (Sources)                        | Supported* | *JavaScript Transformation currently not applied to pulled data<br/><br/>In SQL transaction:<br/>1. Delete previous data for current sync interval: `DELETE FROM destination_table WHERE _time_interval=?`<br/>2. See **Push events (API keys) batch mode**                                                                                                                                                                                           |
| User Recognition<br/>**stream mode**         | Supported  | Primary key on `eventn_ctx_event_id` field is required for destination table.<br/>`UPDATE destination_table SET .. WHERE eventn_ctx_event_id=?`                                                                                                                                                                                                                                                                                                       |
| User Recognition<br/>**batch mode**          | Supported  | Primary key on `eventn_ctx_event_id` field is required for destination table.<br/>Same as **Push events (API keys) batch mode**                                                                                                                                                                                                                                                                                                                       |
